
package common;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class CommonType {
	Utils utils = null;
	public CommonType(Utils utils) {
		this.utils = utils;
	}
	/**
	 * 
	 * @return map 副料項目代號-中文名字.
	 * @throws Exception.
	 */
	public Map agtCus() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		try {
			//sql = "select itm,nam from itm where itm like 'B%' order by itm,nam";
			sql = "select agt_no,cus_no,cus from js_system..wprg_agt_cust order by agt,cus";
			rs = utils.executeQuery("info",sql);
			String agt = "";
			String cus = "";
			while(rs.next()) {
				if(!agt.equals("")) {
					if(!agt.equals(utils.getRsString(rs,"agt_no"))) {
						map.put(agt,cus);
						cus = "";
					}
				}
				agt = utils.getRsString(rs,"agt_no");
				cus += "," + utils.getRsString(rs,"cus_no") + "|" + utils.getRsString(rs,"cus");
			}
			map.put(agt,cus);
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 副料項目代號-中文名字.
	 * @throws Exception.
	 */
	public Map itm() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		try {
			
			//sql = "select itm,nam from itm where itm like 'B%' order by itm,nam";
			sql = "select * from ynsub..sub_item";
			rs = utils.executeQuery("info",sql);
			while(rs.next()) {
				map.put(rs.getString("itm"),rs.getString("nam"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 代理商.
	 * @throws Exception.
	 */
	public Map agent() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct agt_no,agt from js_system..wprg_agt_cust";
			//rs = conn.executeQuery(sql);
			rs = utils.executeQuery("info",sql);
			while(rs.next()) {
				map.put(rs.getString("agt_no"),rs.getString("agt"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 客戶.
	 * @throws Exception.
	 */
	public Map custom() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct cus_no,cus from js_system..wprg_agt_cust";
			rs = utils.executeQuery("info",sql);
			while(rs.next()) {
				map.put(rs.getString("cus_no"),rs.getString("cus"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 職稱tsn-職稱.
	 * @throws Exception.
	 */
	public Map roleStn002() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,titlename,srt from HR..hr002 order by srt";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("titlename").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("titlename"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 職稱tsn-職稱.
	 * @throws Exception.
	 */
	public Map roleStn003() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,titlename,srt from HR..hr002 where enab=2 order by srt";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("titlename").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("titlename"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 公司別tsn-公司別.
	 * 
	 * @throws Exception.
	 */
	public Map companyCodeTsn() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,companynamech,srt from HR..hr003a order by srt";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("companynamech").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("companynamech"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 組織tsn-組織全稱.
	 * @throws Exception.
	 */
	/*
	public Map orgTitleSn() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		//Conn conn = new Conn();
		try {
			conn.connect("SQLTEST");
			sql = "select distinct Tsn,ChFullName,lasttsn,srt from HR..hr003 where enab >= '1'  and hr003atsn = '2' order by lasttsn,srt";
			rs = conn.executeQuery(sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("ChFullName").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("ChFullName"));
				}
			}
		} catch(Exception e) {
			conn.rollBack();
			e.printStackTrace();
			throw e;
		} finally {
			conn.commit();
			conn.disconnect();
		}
		return map;
	}
	*/
	/**
	 * 
	 * @return map 組織tsn-組織全稱.
	 * @throws Exception.
	 */
	public Map orgTitleSn(String companyCodeTsn) throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,ChFullName,lasttsn,srt from HR..hr003 where enab <> 0 order by lasttsn,srt";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("ChFullName").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("ChFullName"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	
	
	
	
	/**
	 * 
	 * @return map 職稱-職稱tsn.
	 * @throws Exception.
	 */
	public Map orgTitleSnTwo(String companyCodeTsn) throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			//sql = "select distinct Tsn,ChFullName,lasttsn,srt from HR..hr003 where enab >= '1'  and hr003atsn = '"+companyCodeTsn+"' order by lasttsn,srt";
			sql = "select distinct Tsn,ChFullName,lasttsn,srt from HR..hr003 where enab >= '1' order by lasttsn,srt";

			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("ChFullName").trim())) {
					map.put(rs.getString("ChFullName"),rs.getString("Tsn"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	
	/**
	 * 
	 * @return map 職務tsn-職務.
	 * @throws Exception.
	 */
	public Map roleStn() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		try {
			sql = "select distinct Tsn,nam from HR..hr001 order by tsn";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("nam").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("nam"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 國別tsn-國別.
	 * @throws Exception.
	 */
	public Map addrCity() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,AddrCty from HR..hr007 order by tsn";
			//rs = conn.executeQuery(sql);
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("AddrCty").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("AddrCty"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 教育程度tsn-教育程度.
	 * @throws Exception.
	 */
	public Map eduLev() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,EduLev from HR..hr008 order by tsn";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("EduLev").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("EduLev"));
				}
			}
		} catch(Exception e) {
			
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 護照tsn-護照.
	 * @throws Exception.
	 */
	public Map passportName() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,papersNam from HR..hr009 order by tsn";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("papersNam").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("papersNam"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 組織tsn-組織上層.
	 * @throws Exception.
	 */
	public Map role(String companyCodeTsn) throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		String sqlUp = "";
		sql = "select distinct Tsn,ChFullName,lasttsn,srt from HR..hr003 where enab >= '1' and hr003atsn = '"+companyCodeTsn+"' order by lasttsn,srt";
		sqlUp = "select distinct lasttsn from HR..hr003  where hr003atsn = '"+companyCodeTsn+"'";

		ResultSet rs = utils.executeQuery("info3",sql);
		ResultSet rsUp = utils.executeQuery("info3",sqlUp);
		List<String> list = new ArrayList<String>();
		//Map map = new LinkedHashMap();
		Map<String,List> mapName = new LinkedHashMap<String,List>();
		try {
			while(rs.next()) {
				map.put(rs.getString("tsn"),rs.getString("lasttsn"));
			}
			for(Iterator i = map.entrySet().iterator(); i.hasNext();) {
				Map.Entry me = (Map.Entry)i.next();
				//String mkey = me.getKey().toString();
				list.add(me.getKey().toString());
			}
			while(rsUp.next()) {
				List<Object> listOne = new ArrayList<Object>();
				for(int i = 0; i < list.size(); i++) {
					if(rsUp.getString("lasttsn").equals(map.get(list.get(i)))) {
						listOne.add(list.get(i));
					}
				}
				mapName.put(rsUp.getString("lasttsn"),listOne);
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return mapName;
	}
	/**
	 * 
	 * @return map 廠商編號-廠商名稱.
	 * @throws Exception.
	 */
	//2008/05/17 sulee 添加
	public List roleForAdv(String companyCodeTsn) throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		List list = new ArrayList();
		String sql = "";
		sql = "select vdr,nm1  from  jmyarn..vdr  where  substring(vdr,1,1)='M'  order by vdr";
		ResultSet rs = utils.executeQuery("info",sql);
		try {
			while(rs.next()) {
				//map.put(rs.getString("vdr"),rs.getString("nm1"));
				list.add(rs.getString("vdr")+"-"+rs.getString("nm1"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return list;
	}
	/**
	 * 
	 * @return map 組織tsn-人員tsn.
	 * @throws Exception.
	 */
	public Map hr005Tsn() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct hr003tsn,hr005tsn,defau from HR..hr006 where orgshow=1 and enab >= 1";
			rs = utils.executeQuery("info3",sql);
			String hr003Tsn = "";
			String hr005Tsn = "";
			while(rs.next()) {
				int h = 0;
				if(rs.getString("defau").equals("false")) {
					h = 1;
				}
				if(rs.getString("hr003tsn").equals(hr003Tsn)) {
					hr003Tsn = rs.getString("hr003tsn");
					hr005Tsn += "," + rs.getString("hr005tsn");
					if(h == 1) {
						hr005Tsn += ";";
					}
				} else {
					if(!"".equals(hr003Tsn)) {
						map.put(hr003Tsn,hr005Tsn);
					}
					hr003Tsn = rs.getString("hr003tsn");
					hr005Tsn = rs.getString("hr005tsn");
					if(h == 1) {
						hr005Tsn += ";";
					}
				}
				map.put(hr003Tsn,hr005Tsn);
				//map.put(rs.getString("hr003tsn"),rs.getString("hr005tsn"));
			}
			
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 人員tsn-組織tsn.
	 * @throws Exception.
	 */
	public Map hr003Tsn() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct hr003tsn,hr005tsn,defau from HR..hr006 where orgshow=1 and enab >= 1";
			rs = utils.executeQuery("info3",sql);
			String hr003Tsn = "";
			String hr005Tsn = "";
			while(rs.next()) {
				int h = 0;
				if(rs.getString("defau").equals("false")) {
					h = 1;
				}
				if(rs.getString("hr003tsn").equals(hr003Tsn)) {
					hr003Tsn = rs.getString("hr003tsn");
					hr005Tsn += "," + rs.getString("hr005tsn");
					if(h == 1) {
						hr005Tsn += ";";
					}
				} else {
					if(!"".equals(hr003Tsn)) {
						map.put(hr003Tsn,hr005Tsn);
					}
					hr003Tsn = rs.getString("hr003tsn");
					hr005Tsn = rs.getString("hr005tsn");
					if(h == 1) {
						hr005Tsn += ";";
					}
				}
				map.put(hr005Tsn,hr003Tsn);
				//map.put(rs.getString("hr003tsn"),rs.getString("hr005tsn"));
			}
			
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	
	/**
	 * 
	 * @return map 人員tsn-職稱tsn.
	 * @throws Exception.
	 */
	public Map hrTitleName() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,hr002tsn from HR..hr005 order by tsn";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("hr002tsn").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("hr002tsn"));
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 人員tsn-中文姓名.
	 * @throws Exception.
	 */
	public Map hrChName() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		//Utils utils = new Utils();
		String sql = "";
		ResultSet rs = null;
		//Map map = new LinkedHashMap();
		try {
			sql = "select distinct Tsn,chname from HR..hr005 order by tsn";
			//rs = conn.executeQuery(sql);
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				//utils.getRsString(rs,"chname")
				if(!"".equals(rs.getString("chname").trim())) {
					map.put(rs.getString("Tsn"),rs.getString("chname").trim());
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 工號-人員tsn.
	 * @throws Exception.
	 */
	public Map hrFactNOToTsn() throws Exception {
		String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select distinct hrnum,Tsn from HR..hr005 order by tsn";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("hrnum").trim())) {
					map.put(rs.getString("hrnum"),rs.getString("tsn").trim());
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map tsn-lasttsn.
	 * @throws Exception.
	 */
	public Map roleTsn() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		sql = "select distinct Tsn,ChFullName,lasttsn,srt from HR..hr003 where enab >= '1' order by tsn";
		ResultSet rs = utils.executeQuery("info3",sql);
		
		try {
			while(rs.next()) {
				map.put(rs.getString("tsn"),rs.getString("lasttsn"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map tsn-工作地.
	 * @throws Exception.
	 */
	public Map workSpace() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select * from HR..hr011 order by tsn";
		try {
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				map.put(rs.getString("tsn"),rs.getString("workPlace"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map tsn-職等.
	 * @throws Exception.
	 */
	public Map workLevel() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select * from HR..hr012 order by tsn";
		try {
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				map.put(rs.getString("tsn"),rs.getString("workLevel"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map tsn-光源名稱.
	 * @throws Exception.
	 */
	public List lightName() throws Exception {
		List list = new ArrayList();
		String sql = "";
		ResultSet rs = null;
		sql = "select * from sys..sys021 order by tsn";
		try {
			rs = utils.executeQuery("sqltest",sql);
			while(rs.next()) {
				list.add(rs.getString("lightName"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return list;		
	}
	
	/**
	 * 
	 * @return map hr004tsn-職務.
	 * @throws Exception.
	 */
	public Map chFullName() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select a.tsn,b.nam from hr..hr004 a,hr..hr001 b where a.hr001tsn = b.tsn";
		try {
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				map.put(rs.getString("tsn"),rs.getString("nam"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map hr001tsn-職務.
	 * @throws Exception.
	 */
	public Map chFullName001() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select * from hr..hr001";
		try {
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				map.put(rs.getString("tsn"),rs.getString("nam"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}	
	/**
	 * 
	 * @return map 組織--一級審核人員.
	 * @throws Exception.
	 */
	public Map verifyLevOne() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select * from hr..hr010 a ,hr..hr005 b where a.hr005tsn = b.tsn and verifyType = '0' and verifylev='1'";
		try {
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("hrnum"))) {
					map.put(rs.getString("hr003tsn"),rs.getString("chname")+"("+rs.getString("hrnum")+")");
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			
		}
		return map;
	}
	/**
	 * 
	 * @return map 組織--二級審核人員.
	 * @throws Exception.
	 */
	public Map verifyLevTwo() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select * from hr..hr010 a ,hr..hr005 b where a.hr005tsn = b.tsn and verifyType = '0' and verifylev='2'";
		try {
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				if(!"".equals(rs.getString("hrnum"))) {
					map.put(rs.getString("hr003tsn"),rs.getString("chname")+"("+rs.getString("hrnum")+")");
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 組織--加班上線小時數.
	 * @throws Exception.
	 */
	public Map overMaxHour() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select * from hr..hr028 order by tsn";
		try {
			rs = utils.executeQuery("info3",sql);
			String jobCode = "";
			int i = 1;
			while(rs.next()) {
				//if(!"".equals(rs.getString("hrnum"))) {
				jobCode += rs.getString("jobcode")+":<font color=#FF0000>"+rs.getString("maxhour")+"</font>";
				if((i - 4) == 0) {
					i = 0;
					map.put(rs.getString("hr003tsn"),jobCode);
					jobCode = "";
				}
				//}
				i++;
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 工號-人員姓名.
	 * @throws Exception.
	 */
	public Map hrFactNOToChname() throws Exception {
		String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select  hrnum,chname from HR..hr005 order by tsn";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				
				if(!"".equals(rs.getString("hrnum").trim())) {
					//System.out.println(rs.getString("chname").trim());
					map.put(rs.getString("hrnum").trim(),rs.getString("chname").trim());
					//map.put(rs.getString("hrnum").trim(),"aa");
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map sys001tsn-sys001lasttsn.
	 * @throws Exception.
	 */
	public Map tsnToLasttsn() throws Exception {
		String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select * from sys..sys001 where ftsn = 0 order  by  tsn";
			rs = utils.executeQuery("sqltest",sql);
			while(rs.next()) {
				if(!"0".equals(rs.getString("ftsn").trim())) {
					map.put(rs.getString("tsn"),rs.getString("nam").trim());
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
	}
	/**
	 * 
	 * @return map 中文-越南.
	 * @throws Exception.
	 */
	public Map chToVn() throws Exception {
		String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select * from sys..sys100";
			rs = utils.executeQuery("sqltest",sql);
			while(rs.next()) {
				//if(!"0".equals(rs.getString("ftsn").trim())) {
					map.put(rs.getString("ch").trim(),rs.getString("vn").trim());
				//}
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
	}
	 /**
     * 異常費用--獲得簽核主管
     * @return ycfy001tsn--簽核人
     * @throws exception
     * @author sime
     */
    public Map ycfycheck() throws Exception { 
    	String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select a.tsn,c.chname,b.verifylev,case when b.defau=0 then '簽核中' when b.defau=1 then '簽核ok' when b.defau=2 then '簽核不ok'  else '簽核異常' end as defau from prod..pd_ycfy001 a,prod..pd_ycfy004 b,hr..hr005 c where b.hrnum = c.hrnum and a.tsn = b.ycfy001tsn order by a.tsn,b.verifylev";
			rs = utils.executeQuery("info5",sql);
			String ycfy001L = "";
			String checkL = "";
			while(rs.next()) {
				if(!"".equals(checkL) && !checkL.equals(rs.getString("tsn").trim())) {
					map.put(checkL,ycfy001L);
					ycfy001L = "";
				}
				checkL = rs.getString("tsn").trim();
				ycfy001L += rs.getString("chname").trim() + rs.getString("defau").trim()+";";
			}
			map.put(checkL,ycfy001L);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
    }
    /**
     * 短裝簽核費用--獲得簽核主管
     * @return ycfy001tsn--簽核人
     * @throws exception
     * @author yhlee
     */
    public Map shipcheck() throws Exception { 
    	String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select ltrim(rtrim(ship002tsn))+'#'+ltrim(rtrim(zr_dept)) as tsn,qh_psn as chname,defau=case when leaderchk=0 then '未簽核' when leaderchk='1' then '簽核不ok' when leaderchk='2' then '簽核ok' end from js_shp..ship002c ";
			rs = utils.executeQuery("info6",sql);
			String ycfy001L = "";
			String checkL = "";
			while(rs.next()) {
				if(!"".equals(checkL) && !checkL.equals(rs.getString("tsn").trim())) {
					map.put(checkL,ycfy001L);
					ycfy001L = "";
				}
				checkL = rs.getString("tsn").trim();
				ycfy001L += rs.getString("chname").trim() + rs.getString("defau").trim()+";";
			}
			map.put(checkL,ycfy001L);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
    }
    /**
     * 異常簽核費用--獲得簽核主管
     * @return ycfy001tsn--簽核人
     * @throws exception
     * @author yhlee
     */
    public Map pscheck() throws Exception { 
    	String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select ltrim(rtrim(ps032tsn))+'#'+ltrim(rtrim(zr_dept)) as tsn,qh_psn as chname,defau=case when leaderchk=0 then '未簽核' when leaderchk='1' then '簽核不ok' when leaderchk='2' then '簽核ok' end from ps..ps032a ";
			rs = utils.executeQuery("info3",sql);
			String ycfy001L = "";
			String checkL = "";
			while(rs.next()) {
				if(!"".equals(checkL) && !checkL.equals(rs.getString("tsn").trim())) {
					map.put(checkL,ycfy001L);
					ycfy001L = "";
				}
				checkL = rs.getString("tsn").trim();
				ycfy001L += rs.getString("chname").trim() + rs.getString("defau").trim()+";";
			}
			map.put(checkL,ycfy001L);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
    }
    /**
     * 異常單號對應處理
     * @return ycfy001tsn--簽核人
     * @throws exception
     * @author sime
     */
    public Map ycfyitem() throws Exception { 
    	String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select tsn,item,citem from prod..pd_ycfy_item";
			rs = utils.executeQuery("info5",sql);
			while(rs.next()) {
				map.put(rs.getString("tsn").trim(),rs.getString("item").trim() + rs.getString("citem").trim());
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
    }
    /**
     * 工號--郵箱
     * @return hrnum--email
     * @throws exception
     * @author sime
     */
    public Map hrnumemail() throws Exception { 
    	String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select hrnum,email from hr..hr005 where leaveday=''";
			rs = utils.executeQuery("info3",sql);
			while(rs.next()) {
				map.put(rs.getString("hrnum").trim(),rs.getString("email").trim());
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
    }
    /**
     * 樣本型號---對應tsn
     * @return style--styletsn
     * @throws exception
     * @author sime
     */
    public Map styletsn() throws Exception { 
    	String sql = "";
		ResultSet rs = null;
		Map<String,String> map = new LinkedHashMap<String,String>();
		try {
			sql = "select style,styletsn from pm..pm032";
			rs = utils.executeQuery("info",sql);
			while(rs.next()) {
				map.put(rs.getString("style").trim(),rs.getString("styletsn").trim());
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
		}
		return map;
    }
    /**
	 * 
	 * @return map 組織--部門加班預設上限.
	 * @throws Exception.
	 */
	public Map reSetMaxHour() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select *,case when isall = 0 then '否' else '是' end as chk from hr..hr015q where startday<=convert(char(10),getdate(),121) and endday>=convert(char(10),getdate(),121)";
		try {
			rs = utils.executeQuery("info3",sql);
			String jobCode = "";
			while(rs.next()) {
				//if(!"".equals(rs.getString("hrnum"))) {
				jobCode = "平日加班:<font color=#FF0000>"+rs.getString("hour1")+"</font>;周末加班:<font color=#FF0000>"+rs.getString("hour2")+"</font>;周末正常上班:<font color=#FF0000>"+rs.getString("hour3")+"</font>;<font color=#FF0000>是否包含子部門:"+rs.getString("chk")+"</font>";
				map.put(rs.getString("hr003tsn"),jobCode);
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}    
	/**
	 * 
	 * @return map 組織--部門加班預設上限.
	 * @throws Exception.
	 */
	public Map reSetMaxHourVn() throws Exception {
		Map<String,String> map = new LinkedHashMap<String,String>();
		String sql = "";
		ResultSet rs = null;
		sql = "select *,case when isall = 0 then '否' else '是' end as chk from hr..hr015q where startday<=convert(char(10),getdate(),121) and endday>=convert(char(10),getdate(),121)";
		try {
			rs = utils.executeQuery("info3",sql);
			String jobCode = "";
			while(rs.next()) {
				//if(!"".equals(rs.getString("hrnum"))) {
				jobCode = "平日加班:<font color=#FF0000>"+rs.getString("hour1")+"</font>;周末加班:<font color=#FF0000>"+rs.getString("hour2")+"</font>;<font color=#FF0000>是否包含子部門:"+rs.getString("chk")+"</font>";
				map.put(rs.getString("hr003tsn"),jobCode);
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return map;
	}
	
	/**
	 * 獲取除國假，周六，周日的天數
	 * @param arge
	 * @throws Exception 
	 */
	public int getDaynumber(Utils utils,int hr003aTsn,String startDay,String endDay ) throws Exception  {
		int days = 0;
		int guojia = 0;
		int satDay = 0;
		int sunDay = 0;
		ResultSet rs = null;
		ResultSet rs1 = null;
	     
		 try {
			if(!startDay.equals("") && !endDay.equals("") && hr003aTsn > 0 ) {
				   String sql = "select countryday from hr..hr027 where hr003aTsn2="+hr003aTsn+"  and hr003aTsn1="+hr003aTsn+"  and countryDay>='"+startDay+"'  and countryDay <='"+endDay+"'";
				   rs = null;
				   rs = utils.executeQuery("info3",sql);
				   while(rs.next()) {
					   rs1 = null;
					   sql = "select hr.dbo.getday('"+rs.getDate("countryday")+"','"+rs.getDate("countryday")+"','星期六') as satday,hr.dbo.getday('"+rs.getDate("countryday")+"','"+rs.getDate("countryday")+"','星期日') as sunday";
					   rs1 = utils.executeQuery("info3",sql);
					   if(rs1.next()) {
						    satDay = rs1.getInt("satday");
						    sunDay = rs1.getInt("sunday");
						   
					   }
					   if(satDay > 0) {
					   } else if(sunDay > 0) {
					   } else {
						   guojia++;
					   }
				   }
				   rs = null;
				   sql = "select hr.dbo.f_WorkDay('"+startDay+"','"+endDay+"') as days";
				   rs = utils.executeQuery("info3",sql);
				   if(rs.next()) {
					   days = rs.getInt("days");
				   }
			 }
			
		} catch (SQLException e) {
			// TODO 自動產生 catch 區塊
			e.printStackTrace();
		} catch (Exception e) {
			// TODO 自動產生 catch 區塊
			e.printStackTrace();
		}
		return days - guojia;
	}
	
	public static void main(String[] arge) {

	}
}
